package com.jurenzhao.partssystem.system.system_log.controller;

import com.jurenzhao.partssystem.system.system_log.aspect.annotation.Token;
import com.jurenzhao.partssystem.system.system_log.entity.System_Log;

import com.jurenzhao.partssystem.system.system_log.service.LogService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@Controller
@RequestMapping("/system_log")
public class System_LogController {
    @Resource
    private LogService system_logService;
    @RequestMapping("/delete")

    public @ResponseBody String delete(int[] id ){
        for(int www :id){
            System.out.println(www);
            system_logService.delete(www);
        }
        //System.out.println(id);
        return "redirect:/system_user/log";
    }
    @RequestMapping("/findByType")
    public String findByType(Model model){
        List<System_Log> system_logs = system_logService.findByType();
        model.addAttribute("system_logs",system_logs);
        return "System/log";
    }
    //上传Excel
    @RequestMapping("/uploadExcel")
    public String uploadExcel(@RequestParam MultipartFile file, HttpServletRequest request) throws Exception {

        if(!file.isEmpty()){
            String filePath = file.getOriginalFilename();
            //windows
            String savePath = "D:\\demo\\"+filePath;

            //linux
            //String savePath = "/home/odcuser/webapps/file";

            File targetFile = new File(savePath);

            if(!targetFile.exists()){
                targetFile.mkdirs();
            }

            file.transferTo(targetFile);
//            List<System_Log> system_logs = readExcel(filePath);
//            for (System_Log system_log: system_logs){
//                System.out.println(system_log);
//                system_logService.insert(system_log);
//            }
            readExcel();
        }

        return "redirect:/system_user/log";
    }
    public static void readExcel() throws Exception{

        String fileName = "D:\\demo\\f2c36a49-0f1f-47d9-a8c3-b6eb9d15f679.xls";
        InputStream is = new FileInputStream(new File(fileName));
        Workbook hssfWorkbook = null;
        if (fileName.endsWith("xlsx")){
            hssfWorkbook = new XSSFWorkbook(is);//Excel 2007
        }else if (fileName.endsWith("xls")){
            hssfWorkbook = new HSSFWorkbook(is);//Excel 2003

        }
        // HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        // XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
        System_Log system_log = null;
        List<System_Log> system_logs = new ArrayList<System_Log>();
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet <hssfWorkbook.getNumberOfSheets(); numSheet++) {
            //HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                //HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                Row hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    system_log = new System_Log();
                    //HSSFCell name = hssfRow.getCell(0);
                    //HSSFCell pwd = hssfRow.getCell(1);
                    Cell type = hssfRow.getCell(1);
                    Cell name = hssfRow.getCell(2);
                    Cell ip = hssfRow.getCell(3);
                    Cell info = hssfRow.getCell(4);
                    Cell state = hssfRow.getCell(5);
                    Cell date = hssfRow.getCell(6);
                    //这里是自己的逻辑
                    system_log.setLogType(type.toString());
                    system_log.setLogUser(name.toString());
                    system_log.setLogIp(ip.toString());
                    system_log.setLogInfo(info.toString());
                    system_log.setLogState(state.toString());
                    SimpleDateFormat sdf=new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
                    sdf.setLenient(false);
                    String sdate=date.toString();
                    Date ddate;
                    ddate=sdf.parse(sdate);
                    system_log.setLogDate(ddate);
                    system_logs.add(system_log);
                }
            }
        }
    }

    //创建Excel
    @RequestMapping("/createExcel")
    public String createExcel(HttpServletResponse response) throws IOException {
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet=wb.createSheet("成绩表");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
        //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell=row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("日志表");
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
        //在sheet里创建第二行
        HSSFRow row2=sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("编号");
        row2.createCell(1).setCellValue("类型");
        row2.createCell(2).setCellValue("名字");
        row2.createCell(3).setCellValue("IP");
        row2.createCell(4).setCellValue("详情");
        row2.createCell(5).setCellValue("种类");
        row2.createCell(6).setCellValue("时间");
        //将LOG表中数据全查出来
        List<System_Log> system_logs = system_logService.findAll();

        //遍历添加
        int id = 0;String type = null; String user = null;String ip =null ; String info =null;String state = null;Date date =null;
        int i =2;

        for (System_Log system_log:system_logs){
            id = system_log.getLogId();
            type =system_log.getLogTypeString();
            user = system_log.getLogUser();
            ip = system_log.getLogIp();
            info= system_log.getLogInfo();
            state = system_log.getLogStateString();
            date = system_log.getLogDate();
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sdf1= sdf.format(date);
            //在sheet里创建第三+i行
            HSSFRow row3 = sheet.createRow(i);
            //HSSFRow row3=sheet.createRow(2);
            HSSFCell cell2= row3.createCell(0);
            cell2.setCellValue(id);
            cell2= row3.createCell(1);
            cell2.setCellValue(type);
            cell2= row3.createCell(2);
            cell2.setCellValue(user);
            cell2= row3.createCell(3);
            cell2.setCellValue(ip);
            cell2= row3.createCell(4);
            cell2.setCellValue(info);
            cell2= row3.createCell(5);
            cell2.setCellValue(state);
            cell2= row3.createCell(6);
            cell2.setCellValue(sdf1);
            i++;
        }




//            row3.createCell(0).setCellValue("李明");
//            row3.createCell(1).setCellValue("As178");
//            row3.createCell(2).setCellValue(87);
//            row3.createCell(3).setCellValue(78);
//            row3.createCell(4).setCellValue(78);
//            row3.createCell(5).setCellValue(78);
//            row3.createCell(6).setCellValue(78);
       //.....省略部分代码
        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        String filename = null;
        String str = "attachment; filename=";
        String str2= UUID.randomUUID().toString();
        String str3 = ".xls";
        filename = str + str2+str3;
        response.setHeader("Content-disposition", filename);
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
        return null;
    }

}
